using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Xml;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using System.Text.RegularExpressions;

namespace Microsoft.SharedSource.SqlServer.MDDE.Extensions
{
    internal sealed class InstanceCreatorMSOIndividual_New :
        TemplateInstanceCreator
    {
        public InstanceCreatorMSOIndividual_New()
        {
            tableInfo = new Dictionary<string, string[]>();

            // Fill in the validation information.
            tableInfo.Add("Source_System",
                new string[] { 
                "SourceSystemID", 
                "SourceSystemName", 
                "ServerName", 
                "DatabaseName" });
            tableInfo.Add("System_Query",
                new string[]{
                "SourceSystemID",
                "TableName",
                "LookupColumns",
                "DateColumnName",
                "WhereCondition",
                "InTableFilter",
                "UpdateColumns",
                "UpdateFilterMatchColumns",
                "UpdateFilterExcludeColumns"});

            // Initialize the get instance query.
            getInstancesQuery = "SELECT TableName FROM System_Query sq JOIN Source_System ss ON ss.SourceSystemID = sq.SourceSystemID WHERE SourceSystemName = 'MSO' AND TableName IN ('Individual','IndividualAddress','IndividualDataSourceTxlat','IndividualEmail','IndividualOrganization','IndividualOrganizationContactType','IndividualPhone','IndividualProfile','IndividualProgramMember','IndividualQuestionResponse','IndividualSubscription','IndividualWizardRegistration','MarketingEventAction','MSproductRegistrationTrans');";
        }

        #region Overridden Members

        public override XmlDocument GetInstanceConfigurationData(
            string instanceName)
        {
            XmlDocument configData = new XmlDocument();

            // Prepare a connection string that could be used with OLE DB Provider.
            string suffixedConnectionString =
                ConnectionString;

            if (!suffixedConnectionString.Contains("Provider"))
                suffixedConnectionString += "Provider=SQLNCLI.1;";

            if (!suffixedConnectionString.Contains("Auto Translate"))
                suffixedConnectionString += "Auto Translate=False;";

            // Add the root element.
            XmlElement root = configData.CreateElement("TemplateInstance");
            XmlAttribute xmlName = configData.CreateAttribute("Name");
            root.Attributes.Append(xmlName);
            xmlName.Value = "MSO_" + instanceName;
            configData.AppendChild(root);

            #region Add Configurable Components.

            XmlElement configElement;

            DataSet ds =
                SqlHelper.ExecuteDataset(
                ConnectionString,
                CommandType.Text,
                " SELECT ServerName, DatabaseName, " +
                " SelectColumns, PK, DateColumnName, " +
                " LookupColumns, UpdateColumns, " +
                " UpdateFilterMatchColumns, UpdateFilterExcludeColumns" +
                " FROM System_Query sq JOIN Source_System ss " +
                " ON sq.SourceSystemID = ss.SourceSystemID " +
                " WHERE ss.SourceSystemName = 'MSO' AND " +
                " sq.TableName = '" + instanceName + "';");

            if (ds.Tables[0].Rows.Count == 0)
                return null;

            string sourceServerName =
                ds.Tables[0].Rows[0]["ServerName"].ToString();

            string sourceDatabaseName =
                ds.Tables[0].Rows[0]["DatabaseName"].ToString();

            string selectColumns =
                ds.Tables[0].Rows[0]["SelectColumns"].ToString();

            string lookupColumns =
                ds.Tables[0].Rows[0]["LookupColumns"].ToString();

            string dateColumn =
                ds.Tables[0].Rows[0]["DateColumnName"].ToString();

            string updateColumns =
                ds.Tables[0].Rows[0]["UpdateColumns"].ToString();

            string updateFilterMatchColumns =
                ds.Tables[0].Rows[0]["UpdateFilterMatchColumns"].ToString();

            string updateFilterExcludeColumn =
                ds.Tables[0].Rows[0]["UpdateFilterExcludeColumns"].ToString();

            #region Add MetadataConnectionString

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=MetadataConnectionString");

            AppendConfigurableProperty(
                configData, configElement, "Value", suffixedConnectionString);

            #endregion

            #region Add PartialStagingSQLStatement

            string partialStagingQuery =
                " SELECT " + selectColumns +
                " , [GENEVA_Deleted_Flag], [GENEVA_Staging_Extraction_id] " +
                " FROM [MSO_" + instanceName + "] (NOLOCK)";

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=PartialStagingSQLStatement");

            AppendConfigurableProperty(
                configData, configElement, "Value", partialStagingQuery);

            #endregion

            #region Add PubConnectionString

            // Not using SqlConnectionStringBuilder here 
            // since it does not support keywords like 
            // 'Provider' and 'Auto Translate'.
            Regex initCatFinder =
                new Regex("(.*)(Initial Catalog=)([^;]*);(.*)",
                RegexOptions.IgnoreCase);

            string pubConn =
                initCatFinder.Replace(suffixedConnectionString,
                "$1$2GenevaOne;$4");

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=PubConnectionString");

            AppendConfigurableProperty(
                configData, configElement,
                "Value", pubConn);

            #endregion

            #region Add SourceConnectionString

            string srcConn =
                initCatFinder.Replace(suffixedConnectionString,
                "$1$2" + sourceDatabaseName + ";$4");

            sourceServerName = 
                sourceServerName.Replace(
                ".", System.Environment.MachineName);

            Regex dataSourceFinder =
                new Regex("(.*)(Data Source=)([^;]*);(.*)",
                RegexOptions.IgnoreCase);

            srcConn =
                dataSourceFinder.Replace(srcConn,
                "$1$2" + sourceServerName + ";$4");

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=SourceConnectionString");

            AppendConfigurableProperty(
                configData, configElement,
                "Value", srcConn);

            #endregion

            #region Add SourceQuery

            SqlParameter[] sqlParams = new SqlParameter[5];
            sqlParams[0] = new SqlParameter("SourceSystemName", "MSO");
            sqlParams[1] = new SqlParameter("TableName", instanceName);
            sqlParams[2] = new SqlParameter("ExtractType", 1);
            sqlParams[3] = new SqlParameter("IsDeltaQuery", 0);
            sqlParams[4] = new SqlParameter("LastRefreshDate", "12/30/1899");

            string sourceQuery =
                SqlHelper.ExecuteScalar(
                ConnectionString,
                "GetETL_SourceSystemQuery",
                sqlParams).ToString();

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=SourceQuery");

            AppendConfigurableProperty(
                configData, configElement, "Value", sourceQuery);

            #endregion

            #region Add StagingConnectionString

            string stagingConn =
                initCatFinder.Replace(suffixedConnectionString,
                "$1$2GenevaStaging;$4");

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=StagingConnectionString");

            AppendConfigurableProperty(
                configData, configElement,
                "Value", stagingConn);

            #endregion

            #region Add StagingSQLStatement

            string stagingSqlStatement =
                " SELECT " + selectColumns +
                " , [GENEVA_Deleted_Flag], [GENEVA_Staging_Extraction_id]" +
                " FROM [MSO_" + instanceName + "] (NOLOCK)" +
                " WHERE GENEVA_Staging_Extraction_id=0";

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=StagingSQLStatement");

            AppendConfigurableProperty(
                configData, configElement,
                "Value", stagingSqlStatement);

            #endregion

            #region Add TableName

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=TableName");

            AppendConfigurableProperty(
                configData, configElement,
                "Value", "MSO_" + instanceName);

            #endregion

            #region Add WorkAreaConnectionString

            string waConn =
                initCatFinder.Replace(suffixedConnectionString,
                "$1$2ETLWorkArea;$4");

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Variable=WorkAreaConnectionString");

            AppendConfigurableProperty(
                configData, configElement,
                "Value", waConn);

            #endregion

            #region Add Get Source query from meta

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Get Source query from meta");

            AppendConfigurableProperty(
                configData, configElement,
                "SqlStatementSource",
                "[GetETL_SourceSystemQuery] 'MSO','" +
                instanceName + "',?,0, ?");

            #endregion

            #region Add Truncate Staging Table

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Truncate Tables/Executable=Truncate Staging Table");

            AppendConfigurableProperty(
                configData, configElement,
                "SqlStatementSource",
                "IF EXISTS(SELECT top 1 * from MSO_" +
                instanceName +
                ") BEGIN TRUNCATE TABLE MSO_" + instanceName + " END");

            #endregion

            #region Add Truncate GenevaOne Table

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Truncate Tables/Executable=Truncate GenevaOne Table");

            AppendConfigurableProperty(
                configData, configElement,
                "SqlStatementSource",
                "IF EXISTS(SELECT top 1 * from MSO_" +
                instanceName +
                ") BEGIN TRUNCATE TABLE MSO_" + instanceName + " END");

            #endregion

            #region Add Truncate Workarea

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Truncate Workarea/Executable=Truncate Workarea");

            AppendConfigurableProperty(
                configData, configElement,
                "SqlStatementSource",
                "IF EXISTS(SELECT top 1 * from MSO_" +
                instanceName +
                ") BEGIN TRUNCATE TABLE MSO_" + instanceName + " END");

            #endregion

            #region Add Staging

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Full Extract/Executable=Source to Staging/Component=Staging");

            AppendConfigurableProperty(
                configData, configElement,
                "OpenRowset",
                "MSO_" + instanceName);

            AppendMapping(
                configData, configElement, "ExtractionProcessID",
                "True", "GENEVA_Staging_Extraction_id");

            #endregion

            #region Add GenevaOne

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Full Extract/Executable=Staging to GenevaOne (Full)/Component=GenevaOne");

            AppendConfigurableProperty(
                configData, configElement,
                "OpenRowset",
                "MSO_" + instanceName);

            AppendMapping(
                configData, configElement, "GENEVA_Staging_Extraction_id",
                "True", "GENEVA_ONE_Extraction_id");

            #endregion

            #region Add Workarea

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=Source to Workarea/Component=Workarea");

            AppendConfigurableProperty(
                configData, configElement,
                "OpenRowset",
                "MSO_" + instanceName);

            #endregion

            #region Fetch Delta from WorkArea

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=WorkArea to Staging/Component=Fetch Delta from WorkArea");

            AppendConfigurableProperty(
                configData, configElement,
                "SqlCommand",
                "SELECT " + selectColumns + " FROM MSO_" + instanceName);

            #endregion

            #region Add ExtractionID Clone

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=WorkArea to Staging/Component=ExtractionID Clone");

            XmlElement collectionElement =
                AppendColumnCollection(
                configData, configElement,
                "Output", "Derived Column Output");

            XmlElement columnElement =
                AppendColumn(configData, collectionElement, "Derived Column 1");

            AppendConfigurableProperty(
                configData, columnElement,
                "FriendlyExpression", updateFilterExcludeColumn);

            #endregion

            #region Add OLE DB Destination

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=WorkArea to Staging/Component=OLE DB Destination");

            AppendConfigurableProperty(
                configData, configElement,
                "OpenRowset",
                "MSO_" + instanceName);

            AppendMapping(
                configData, configElement, "ExtractionProcessID",
                "True", "GENEVA_Staging_Extraction_id");

            #endregion

            #region Add Staging Command

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=WorkArea to Staging/Component=Staging Command");

            string updateStatement =
                "UPDATE MSO_" + instanceName + " SET ";

            string[] updateCols = updateColumns.Split(',');
            for (int i = 0; i < updateCols.Length; i++)
                updateCols[i] = updateCols[i].Trim();

            string[] updateFilterMatchCols =
                updateFilterMatchColumns.Split(',');
            for (int i = 0; i < updateFilterMatchCols.Length; i++)
                updateFilterMatchCols[i] = updateFilterMatchCols[i].Trim();

            string[] updateFilterExcludeCols =
                updateFilterExcludeColumn.Split(',');
            for (int i = 0; i < updateFilterExcludeCols.Length; i++)
                updateFilterExcludeCols[i] = updateFilterExcludeCols[i].Trim();

            // Prepare the update statement.
            foreach (string updateCol in updateCols)
                updateStatement += updateCol + " = ?, ";

            updateStatement +=
                "GENEVA_Staging_Extraction_id = ? WHERE " +
                updateFilterMatchCols[0] + " = ?";

            for (int idxMatchCol = 1;
                idxMatchCol < updateFilterMatchCols.Length;
                idxMatchCol++)
                updateStatement +=
                    " AND " + updateFilterMatchCols[idxMatchCol] + " = ?";

            for (int idxExcludeCol = 0;
                idxExcludeCol < updateFilterExcludeCols.Length;
                idxExcludeCol++)
                updateStatement +=
                    " AND " + updateFilterExcludeCols[idxExcludeCol] + " <> ?";

            AppendConfigurableProperty(
                configData, configElement,
                "SqlCommand",
                updateStatement);

            // Prepare mappings.
            int idxParam = 0;
            for (int idxUpdateCol = 0;
                idxUpdateCol < updateCols.Length; idxUpdateCol++, idxParam++)
                AppendMapping(
                configData, configElement, updateCols[idxUpdateCol],
                "True", "Param_" + idxParam.ToString());

            // Append the mapping for ExtractionProcessID.
            AppendMapping(
                configData, configElement, "ExtractionProcessID",
                "True", "Param_" + idxParam.ToString());

            idxParam++;

            // Append the update match columns.
            for (int idxMatchCol = 0;
                idxMatchCol < updateFilterMatchCols.Length;
                idxMatchCol++, idxParam++)
                AppendMapping(
                    configData, configElement,
                    updateFilterMatchCols[idxMatchCol],
                    "True", "Param_" + idxParam.ToString());

            // Append the update exclude column.
            AppendMapping(
                configData, configElement,
                "Derived Column 1",
                "True", "Param_" + idxParam.ToString());

            #endregion

            #region Add Lookup

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=Staging to GenevaOne(Delta)/Component=Lookup");

            AppendConfigurableProperty(
                configData, configElement,
                "SqlCommand",
                "SELECT " + lookupColumns +
                " FROM MSO_" + instanceName + " (NOLOCK)");

            collectionElement =
                AppendColumnCollection(
                configData, configElement,
                "Input", "Lookup Input");

            string[] lookupCols = lookupColumns.Split(',');
            for (int i = 0; i < lookupCols.Length; i++)
                lookupCols[i] = lookupCols[i].Trim();

            foreach (string lookupCol in lookupCols)
            {
                columnElement =
                    AppendColumn(configData, collectionElement, lookupCol);

                AppendConfigurableProperty(
                    configData, columnElement,
                    "JoinToReferenceColumn", lookupCol);
            }

            #endregion

            #region OLE DB Destination

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=Staging to GenevaOne(Delta)/Component=OLE DB Destination");

            AppendConfigurableProperty(
                configData, configElement,
                "OpenRowset",
                "MSO_" + instanceName);

            AppendMapping(
                configData, configElement, "GENEVA_Staging_Extraction_id",
                "True", "GENEVA_ONE_Extraction_id");

            #endregion

            #region Add Pub Command

            configElement =
                AppendConfigurableComponent(
                configData, root,
                "Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=Staging to GenevaOne(Delta)/Component=Pub Command");
            updateStatement =
                "UPDATE MSO_" + instanceName + " SET ";

            // Prepare the update statement.
            foreach (string updateCol in updateCols)
                updateStatement += updateCol + " = ?, ";

            updateStatement +=
                "GENEVA_ONE_Extraction_id = ? WHERE " +
                updateFilterMatchCols[0] + " = ?";

            for (int idxMatchCol = 1;
                idxMatchCol < updateFilterMatchCols.Length;
                idxMatchCol++)
                updateStatement +=
                    " AND " + updateFilterMatchCols[idxMatchCol] + " = ?";

            AppendConfigurableProperty(
                configData, configElement,
                "SqlCommand",
                updateStatement);


            // Prepare mappings.
            idxParam = 0;
            for (int idxUpdateCol = 0;
                idxUpdateCol < updateCols.Length; idxUpdateCol++, idxParam++)
                AppendMapping(
                configData, configElement, updateCols[idxUpdateCol],
                "True", "Param_" + idxParam.ToString());

            // Append the mapping for GENEVA_Staging_Extraction_id.
            AppendMapping(
                configData, configElement, "GENEVA_Staging_Extraction_id",
                "True", "Param_" + idxParam.ToString());

            idxParam++;

            // Append the update match columns.
            for (int idxMatchCol = 0;
                idxMatchCol < updateFilterMatchCols.Length;
                idxMatchCol++, idxParam++)
                AppendMapping(
                    configData, configElement,
                    updateFilterMatchCols[idxMatchCol],
                    "True", "Param_" + idxParam.ToString());

            #endregion

            #endregion

            return configData;
        }

        #endregion
    }
}
